Imports System.Data.SqlClient
Imports System.Data
Imports DevExpress.XtraExport
Imports System.Text
Imports DevExpress.XtraGrid.Export
'0000
Public Class RedemRecord

    Private Sub RedemRecord_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        LoadIcon(Me)
        refreshGrid()
    End Sub

    Private Sub refreshGrid()
        Dim str As String = "SELECT * FROM RedemptionRecord_View"
        Dim cmd As New SqlCommand(str, gCnnPCE)
        Dim da As New SqlDataAdapter(cmd)
        Dim ds As New DataSet
        da.Fill(ds, "RedemptionRecord")
        Me.GridControl1.DataSource = ds.Tables(0)

        Me.lblID.DataBindings.Clear()
        lblID.DataBindings.Add(New Binding("Text", ds.Tables("RedemptionRecord"), "ID", True))

    End Sub

    Private Sub rbFilterBy_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbFilterBy.SelectedIndexChanged
        Dim str As String
        Dim cmd As New SqlCommand("", gCnnPCE)

        Select Case rbFilterBy.EditValue
            Case 0 ' Daily
                str = "SELECT * FROM RedemptionRecord_View WHERE CreatedDateTime > @DateFrom AND CreatedDateTime < @DateTo"
                cmd.CommandText = str
                Dim Today_DateTime_From As New Date(Now.Year, Now.Month, Now.Day, 0, 0, 0)
                Dim Today_DateTime_To As New Date(Now.Year, Now.Month, Now.Day, 23, 59, 59)
                cmd.Parameters.Add("@DateFrom", SqlDbType.DateTime).Value = Today_DateTime_From
                cmd.Parameters.Add("@DateTo", SqlDbType.DateTime).Value = Today_DateTime_To

            Case 1 ' Weekly
                str = "SELECT * FROM RedemptionRecord_View WHERE CreatedDateTime > @DateFrom AND CreatedDateTime < @DateTo"
                cmd.CommandText = str
                Dim DateTime_From As Date
                Dim DateTime_To As Date
                Select Case Now.DayOfWeek
                    Case 1
                        DateTime_From = New Date
                        DateTime_From = Now.AddHours(-12)
                        DateTime_To = New Date
                        DateTime_To = Now.AddDays(6)
                        DateTime_To = DateTime_To.AddHours(12)
                    Case 2
                        DateTime_From = New Date
                        DateTime_From = Now.AddDays(-1)
                        DateTime_From = DateTime_From.AddHours(-12)
                        DateTime_To = New Date
                        DateTime_To = Now.AddDays(5)
                        DateTime_To = DateTime_To.AddHours(12)
                    Case 3
                        DateTime_From = New Date
                        DateTime_From = Now.AddDays(-2)
                        DateTime_From = DateTime_From.AddHours(-12)
                        DateTime_To = New Date
                        DateTime_To = Now.AddDays(4)
                        DateTime_To = DateTime_To.AddHours(12)
                    Case 4
                        DateTime_From = New Date
                        DateTime_From = Now.AddDays(-3)
                        DateTime_From = DateTime_From.AddHours(-12)
                        DateTime_To = New Date
                        DateTime_To = Now.AddDays(3)
                        DateTime_To = DateTime_To.AddHours(12)
                    Case 5
                        DateTime_From = New Date
                        DateTime_From = Now.AddDays(-4)
                        DateTime_From = DateTime_From.AddHours(-12)
                        DateTime_To = New Date
                        DateTime_To = Now.AddDays(2)
                        DateTime_To = DateTime_To.AddHours(12)
                    Case 6
                        DateTime_From = New Date
                        DateTime_From = Now.AddDays(-5)
                        DateTime_From = DateTime_From.AddHours(-12)
                        DateTime_To = New Date
                        DateTime_To = Now.AddDays(1)
                        DateTime_To = DateTime_To.AddHours(12)
                    Case 7
                        DateTime_From = New Date
                        DateTime_From = Now.AddDays(-6)
                        DateTime_From = DateTime_From.AddHours(-12)
                        DateTime_To = New Date
                        DateTime_To = DateTime_To.AddHours(12)
                End Select
                cmd.Parameters.Add("@DateFrom", SqlDbType.DateTime).Value = DateTime_From
                cmd.Parameters.Add("@DateTo", SqlDbType.DateTime).Value = DateTime_To

            Case 2 'Monthly

                str = "SELECT * FROM RedemptionRecord_View WHERE CreatedDateTime > @DateFrom AND CreatedDateTime < @DateTo"
                cmd.CommandText = str
                Dim DateTime_From As New Date(Now.Year, Now.Month, 1, 0, 0, 0)
                Select Case Now.Month
                    Case 1, 3, 5, 7, 8, 10, 12
                        Dim DateTime_To As New Date(Now.Year, Now.Month, 31, 23, 59, 59)
                        cmd.Parameters.Add("@DateFrom", SqlDbType.DateTime).Value = DateTime_From
                        cmd.Parameters.Add("@DateTo", SqlDbType.DateTime).Value = DateTime_To
                    Case 2
                        Dim DateTime_To As New Date(Now.Year, Now.Month, 30, 23, 59, 59)
                        cmd.Parameters.Add("@DateFrom", SqlDbType.DateTime).Value = DateTime_From
                        cmd.Parameters.Add("@DateTo", SqlDbType.DateTime).Value = DateTime_To
                    Case 4, 6, 9, 11
                        Dim DateTime_To As New Date(Now.Year, Now.Month, 28, 23, 59, 59)
                        cmd.Parameters.Add("@DateFrom", SqlDbType.DateTime).Value = DateTime_From
                        cmd.Parameters.Add("@DateTo", SqlDbType.DateTime).Value = DateTime_To
                End Select



            Case 3 'Yearly
                str = "SELECT * FROM RedemptionRecord_View WHERE CreatedDateTime > @DateFrom AND CreatedDateTime < @DateTo"
                cmd.CommandText = str
                Dim DateTime_From As New Date(Now.Year, 1, 1, 0, 0, 0)
                Dim DateTime_To As New Date(Now.Year, 12, 31, 23, 59, 59)
                cmd.Parameters.Add("@DateFrom", SqlDbType.DateTime).Value = DateTime_From
                cmd.Parameters.Add("@DateTo", SqlDbType.DateTime).Value = DateTime_To

            Case 4 'All
                str = "SELECT * FROM RedemptionRecord_View "
                cmd.CommandText = str
        End Select

        cmd.CommandText = str
        Dim ds As New DataSet
        Dim da As New SqlDataAdapter(cmd)
        da.Fill(ds, "RedemptionRecord")
        Me.GridControl1.DataSource = ds.Tables(0)

        Me.lblID.DataBindings.Clear()
        lblID.DataBindings.Add(New Binding("Text", ds.Tables("RedemptionRecord"), "ID", True))

    End Sub

    Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click
        Dim saveDialog As New SaveFileDialog
        saveDialog.Title = "Export To "
        saveDialog.Filter = "Microsoft Excel|*.xls|HTML Documents|*html|Text Files|*.txt|XML Document|*.xml"
        If saveDialog.ShowDialog() = Windows.Forms.DialogResult.OK Then
            Try
                Select Case saveDialog.FilterIndex
                    Case 1
                        Call ExportTo(New ExportXlsProvider(saveDialog.FileName), GridView1)
                    Case 2
                        Call ExportTo(New ExportHtmlProvider(saveDialog.FileName), GridView1)
                    Case 3
                        Call ExportTo(New ExportTxtProvider(saveDialog.FileName), GridView1)
                    Case 4
                        Call ExportTo(New ExportXmlProvider(saveDialog.FileName), GridView1)
                End Select
                MsgBox("Successfully Retrive to " & saveDialog.FileName & ".", MsgBoxStyle.Information)
            Catch ex As Exception
                MsgBox("Fail To Retrive : " & ex.Message, MsgBoxStyle.Information)
            End Try

        End If
    End Sub

    Private Sub ExportTo(ByVal provider As DevExpress.XtraExport.IExportProvider, ByVal grid As DevExpress.XtraGrid.Views.Grid.GridView)
        Dim link As BaseExportLink = grid.CreateExportLink(provider)
        CType(link, GridViewExportLink).ExpandAll = False
        link.ExportTo(True)
    End Sub

    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
        Me.Close()
    End Sub
End Class